﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Excel = Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;
using Ext.Net;
using System.IO;
using Viewer.BusinessObject.BusinessEntities; 
namespace Viewer.BaoCao
{
    public partial class Interop : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            
        }

        protected void btn_Click(object sender, EventArgs e)
        {
            List<NSToChuc> lstResult = new List<NSToChuc>();//List to chuc
            lstResult = (new NSToChuc()).GetAll();
            object [,] dataArr = new object[lstResult.Count,165];
            //Intital data
            for (int i = 0; i < dataArr.GetLength(0); i++)
            {
                dataArr[i, 0] = i + 1;
                for (int j = 1; j < dataArr.GetLength(1); j++)
                {
                    dataArr[i, j] = string.Empty;
                }
            }

            //Insert data from databases into dataArr
            //Duyet tung cot
            for (int i = 0; i < dataArr.GetLength(1); i++)
            {
               
            }
            //Export
            ExportToExcel(dataArr, 1);
        }

        /// <summary>
        /// Export To Excel
        /// </summary>
        /// <param name="arr"></param>
        /// <param name="sheetIndex"></param>
        private void ExportToExcel(object[,] arr,int sheetIndex)
        {
           object misValue = System.Reflection.Missing.Value;
           string templatePath = Server.MapPath("~/Baocao/Template/template.xls");
           string newFileName = Context.User.Identity.Name + DateTime.Now.ToString("dd_MM_yyyy_ss") + ".xls";
           string temporaryFilePath = Server.MapPath("~/BaoCao/Template/") + newFileName;
           try
           {
               if (arr == null)
               {
                   throw new Exception("No data inserted");
               }
               if (!File.Exists(templatePath))
               {
                   throw new Exception("Template khong ton tai o dia chi" + templatePath);
               }

               System.IO.FileInfo file = new System.IO.FileInfo(templatePath);
               file.CopyTo(temporaryFilePath);

               Excel.Application xlApp;
               Excel.Workbook xlWorkBook;
               Excel.Worksheet xlWorkSheet;

               Excel.Range copyFormatRange;
               //Create File

               xlApp = new Excel.ApplicationClass();
               if (xlApp == null)
               {
                   throw new Exception("Excel could not be started. Check that your office installation and project references are correct.");
               }
               xlWorkBook = xlApp.Workbooks.Open(temporaryFilePath, misValue, misValue, misValue, misValue, misValue, misValue,
                   misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue);

               //select Sheet
               xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(sheetIndex);
               //Insert Data to the file

               copyFormatRange = xlWorkSheet.get_Range("A10", "FI10");
               copyFormatRange.Select();
               copyFormatRange.Copy(Type.Missing);
               int row = arr.GetLength(0);
               int column = arr.GetLength(1);

               for (int i = 0; i < row; i++)
               {
                   copyFormatRange.Insert(Excel.XlInsertShiftDirection.xlShiftDown, true);
               }

               xlWorkSheet.Cells[9, 10] = string.Format("=Sum(J{0}:J{1})", 10, 10 + row - 1);
               
               int topRow = 10;
               Excel.Range c1 = (Excel.Range)xlWorkSheet.Cells[topRow, 1];
               Excel.Range c2 = (Excel.Range)xlWorkSheet.Cells[topRow + row - 1, column];
               Excel.Range range = xlWorkSheet.get_Range(c1, c2);

               range.Value2 = arr;

               xlWorkBook.Close(true, misValue, misValue);
               xlApp.Quit();

               ReleaseComObject(xlWorkSheet);
               ReleaseComObject(xlWorkBook = null);
               ReleaseComObject(xlApp);
               GC.WaitForPendingFinalizers();
               GC.Collect();
               GC.WaitForPendingFinalizers();
               GC.Collect();

               byte[] document = this.StreamFile(temporaryFilePath);
               Response.Clear();
               Response.Charset = "UTF-8";
               Response.ContentEncoding = System.Text.Encoding.UTF8;
               Response.Cache.SetNoServerCaching();
               Response.AddHeader("Content-Disposition", "attachment; filename=" + newFileName);
               Response.ContentType = "application/ms-excel";
               Response.BinaryWrite(document);
               Response.End();

           }
           catch (Exception)
           {
               throw;
           }
       }
        /// <summary>
        /// Doc file , xong roi xoa file
        /// </summary>
        /// <param name="filename"></param>
        /// <returns></returns>
        private byte[] StreamFile(string filename)
       {
           byte[] ImageData = new byte[0];
           FileStream fs = new FileStream(filename, FileMode.Open, FileAccess.Read);
           try
           {
               ImageData = new byte[fs.Length];
               fs.Read(ImageData, 0, System.Convert.ToInt32(fs.Length));
           }
           catch (Exception)
           {
               throw;
           }
           finally
           { 
            if (fs != null)
            {
                fs.Close();
            }
            System.IO.FileInfo file1 = new System.IO.FileInfo(filename);
            file1.Delete();
           }
           return ImageData;
       }
        /// <summary>
        /// Release Com Object
        /// </summary>
        /// <param name="activeXObject"></param>
        private static void ReleaseComObject(object activeXObject)
        {
            if (activeXObject != null)
            {
                Marshal.ReleaseComObject(activeXObject);
                activeXObject = null;
            }
        }
    }
}
















